Test Data Generation for Killing SQL Mutants

SQL queries are widely used in critical enterprise applications. Complex queries using mutlitable joins and aggregations are difficult to specifiy correctly. Programmers sometimes make mistakes in writing SQL queries, for example using a wrong join operator, a wrong relational operator in a where-clause predicate, etc. We call such variations of the query as ‘mutants’ of the original query. A mutant is killed by a dataset if the original query and the mutant return different results on the dataset. Hence, looking at the results of of a query on a dataset that kills a mutant one can determine if the original query was actually the intended query or the intended query may have been one of the mutants. The traditional way of testing whether SQL queries have been correctly specified include manually checking the results after executing the query against a dataset which itself is manually generated. However, a manually generated dataset may not include sufficient test cases for killing all known mutants of the query. In this report, we look at the problem of test data generation for a SQL query, to kill possible mutants. We present an approach for creation of small test datasets which would kill mutants of a specified query considering a limited class of mutations. These small datasets would enable a human to look at the result of the given query and its mutants to determine if the original query is indeed the intended query. The generated test cases satisfy all database constraints and are also realistic. This is ensured by extracting the test cases from an existing database. In case existing data is not sufficient, data is synthetically generated where required. We focus on a class of join/outer join mutants, which are a common cause of error in complex SQL queries. We present techniques to generate a test suite containing multiple test cases, and prove that under certain assumptions, the test suite is complete, i.e. it kills all non-equivalent mutations, in the class we consider. We also consider some common types of where-clause mutants.